using System.Data;
using System.Windows;
using MySql.Data.MySqlClient;
using WpfApp1.Models;

namespace WpfApp1.DataAccess;

public class CourseDataAccess : ICourseDataAccess
{
    // 查询所有课程信息
    public List<Course> GetCourses()
    {
        var courses = new List<Course>();
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var command1 = new MySqlCommand("SELECT * FROM courses", connection);
            using (var reader1 = command1.ExecuteReader())
            {
                while (reader1.Read())
                {
                    courses.Add(new Course(
                        reader1.GetInt32("course_id"),
                        reader1.GetString("course_number"),
                        reader1.GetString("course_name"),
                        reader1.GetInt32("credits"),
                        reader1.GetInt32("hours"),
                        reader1.GetString("teacher_number")
                    ));
                }
            }
        }
        return courses;
    }

    // 按CourseNumber查询对应课程信息
    public Course GetCourseByNumber(string courseNumber)
    {
        Course course = null!;
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var command1 = new MySqlCommand($"SELECT * FROM courses WHERE course_number='{courseNumber}'", connection);
            using (var reader1 = command1.ExecuteReader())
            {
                while (reader1.Read())
                {
                    course = new Course(
                        reader1.GetInt32("course_id"),
                        reader1.GetString("course_number"),
                        reader1.GetString("course_name"),
                        reader1.GetInt32("credits"),
                        reader1.GetInt32("hours"),
                        reader1.GetString("teacher_number")
                    );
                }
            }
        }
        return course;
    }

    // 添加课程信息
    public void AddCourse(Course course)
    {
        if (String.IsNullOrEmpty(course.CourseNumber) || 
            String.IsNullOrEmpty(course.CourseName) || 
            String.IsNullOrEmpty(course.TeacherNumber) ||
            course.Credits == -1 || 
            course.Hours == -1)
        {
            MessageBox.Show("必要信息为空");
            return;
        }

        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            
            // 先检查课程编号是否存在
            var checkSql = "SELECT COUNT(*) FROM courses WHERE course_number = @course_number";
            var checkCommand = new MySqlCommand(checkSql, connection);
            checkCommand.Parameters.AddWithValue("@course_number", course.CourseNumber);
            var count = Convert.ToInt32(checkCommand.ExecuteScalar());
            
            if (count > 0)
            {
                MessageBox.Show("该课程编号已存在！");
                return;
            }

            // 课程编号不存在，执行插入操作
            var sql = @"INSERT INTO courses(course_number, course_name, credits, hours, teacher_number) 
                    VALUES(@course_number, @course_name, @credits, @hours, @teacher_number)";
            
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@course_number", course.CourseNumber);
            command.Parameters.AddWithValue("@course_name", course.CourseName);
            command.Parameters.AddWithValue("@credits", course.Credits);
            command.Parameters.AddWithValue("@hours", course.Hours);
            command.Parameters.AddWithValue("@teacher_number", course.TeacherNumber);
        
            var row = command.ExecuteNonQuery();
            MessageBox.Show(row == 1 ? "添加成功！" : "添加失败！");
        }
    }

    // 根据课程number修改课程信息
    public void UpdateCourse(Course course)
    {
        if (String.IsNullOrEmpty(course.CourseNumber) || 
            String.IsNullOrEmpty(course.CourseName) || 
            String.IsNullOrEmpty(course.TeacherNumber) ||
            course.Credits == -1 || 
            course.Hours == -1)
        {
            MessageBox.Show("必要信息为空");
            return;
        }

        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var sql = @"UPDATE courses 
                    SET course_name = @course_name, 
                        credits = @credits, 
                        hours = @hours, 
                        teacher_number = @teacher_number 
                    WHERE course_number = @course_number";
            
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@course_number", course.CourseNumber);
            command.Parameters.AddWithValue("@course_name", course.CourseName);
            command.Parameters.AddWithValue("@credits", course.Credits);
            command.Parameters.AddWithValue("@hours", course.Hours);
            command.Parameters.AddWithValue("@teacher_number", course.TeacherNumber);
            
            var row = command.ExecuteNonQuery();
            MessageBox.Show(row == 1 ? "修改成功！" : "修改失败！");
        }
    }

    // 根据课程number删除课程信息
    public void DeleteCourse(Course course)
    {
        if (String.IsNullOrEmpty(course.CourseNumber))
        {
            MessageBox.Show("必要信息为空");
            return;
        }

        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            String sql = $"delete from courses where course_number = '{course.CourseNumber}'";
            var command = new MySqlCommand(sql, connection);
            command.ExecuteNonQuery();
        }
    }
}